﻿Public Class ListInOut2

    Private Sub LoadData()

        Dim date1 As DateTime = New Date(dtFindDate.Value.Year, dtFindDate.Value.Month, dtFindDate.Value.Day)
        Dim date2 As DateTime = New Date(dtFindDate2.Value.Year, dtFindDate2.Value.Month, dtFindDate2.Value.Day)

        If date1 > date2 Then
            Me.DataGridView1.DataSource = Nothing
            Exit Sub
        End If

        Dim dt As New DataTable
        Dim dc As DataColumn
        dt.Columns.Add("月份")
        dc = dt.Columns.Add("收入", GetType(Decimal))
        dc.DefaultValue = 0

        dt.Columns.Add("支出", GetType(Decimal))
        dt.Columns.Add("统计", GetType(Decimal))

        '抽数据
        Dim Year1 As Integer = Me.dtFindDate.Value.Year
        Dim Year2 As Integer = Me.dtFindDate2.Value.Year

        Dim strSql As String = String.Empty
        Dim strSql1 As String = String.Empty
        Dim dr As DataRow

        For Year As Integer = Year1 To Year2
            Dim strBegin As String = Year.ToString("0000") + "-" + 1.ToString("00") + "-" + 1.ToString("00")
            Dim strEnd As String = Year.ToString("0000") + "-" + 12.ToString("00") + "-" + 31.ToString("00")
            strSql = "select  sum(count) as count from WysInput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"
            strSql1 = "select  sum(count) as count from WysOutput where date([DateTime])>='" + strBegin + "' AND date([DateTime])<='" + strEnd + "'"

            dr = dt.NewRow

            dr(0) = Year.ToString + "年"

            Dim dt2 As DataTable = ct.FillTable(strSql)
            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                dr(1) = 结果(dt2.Rows(0)(0))
            Else
                dr(1) = 0
            End If
            dt2 = ct.FillTable(strSql1)
            If dt2 IsNot Nothing AndAlso dt2.Rows.Count > 0 Then
                dr(2) = 结果(dt2.Rows(0)(0))
            Else
                dr(2) = 0
            End If
            dr(3) = CDec(dr(1)) - CDec(dr(2))
            dt.Rows.Add(dr)
        Next

        dr = dt.NewRow
        dr(1) = 0
        dr(2) = 0
        dr(3) = 0

        dr(0) = "合计"
        For i As Integer = 0 To dt.Rows.Count - 1
            dr(1) += 结果(dt.Rows(i)(1))
            dr(2) += 结果(dt.Rows(i)(2))
            dr(3) += 结果(dt.Rows(i)(3))
        Next
        dt.Rows.Add(dr)
        Me.DataGridView1.DataSource = dt
    End Sub

    Private Sub ListInOut_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim dt As DataTable = ct.FillTable("select  sum(count) as count from WysInput")
        If dt.Rows.Count > 0 Then
            Me.txt总收入.Text = dt.Rows(0)("count")
        End If
        dt = ct.FillTable("select  sum(count) as count from WysOutput ")
        If dt.Rows.Count > 0 Then
            Me.txt总支出.Text = dt.Rows(0)("count")
        End If

        LoadData()
    End Sub

    Public Function 结果(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            ob = 0
        End If
        Return CDec(ob)
    End Function

    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        dtFindDate.Value = dtFindDate.Value.AddYears(1)
        'LoadData()
    End Sub

    Private Sub btnPre_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPre.Click
        dtFindDate.Value = dtFindDate.Value.AddYears(-1)
        'LoadData()
    End Sub

    Private Sub dtFindDate_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dtFindDate2.ValueChanged, dtFindDate.ValueChanged
        LoadData()
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dtFindDate2.Value = dtFindDate2.Value.AddYears(-1)
        'LoadData()
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        dtFindDate2.Value = dtFindDate2.Value.AddYears(1)
        'LoadData()
    End Sub
End Class
